Loading OData with Excel 2016

Depending on the version of Microsoft Excel you are using, the steps to load OData can be slightly different. If you're working with Excel 2010 or 2013, see Loading OData with Excel 2010, 2013.

Load Data with Excel 2016

Follow these steps to load OData using Excel 2016:

  1. Click on the Data tab.
  2. In the Get & Transform group, click New Query, hover over From Other Sources, and select From OData Feed.

    3. Click image to expand or minimize.

  3. The OData Feed window will open.
  4. With the Basic option selected, copy a feed URL from The Instances Page, add it to the URL field, and click OK.

    5. Click image to expand or minimize. 

    If you're accessing a feed URL for the first time, you may have to manually select an authentication method to access the OData feed. See Authentication Errors later on this page for more information.
  5. A summary table will appear that lists the objects in the result set you are loading, along with the types of data that can be downloaded for each object. In the example below, attribute, scalar, and vector data can be downloaded for each object.

    6. Click image to expand or minimize. 

    This table will only display a maximum of 20 objects at a time — even though your result set may include more objects. Access the Query Editor to view all of the objects included in your result set.
  6. Click the Edit button to open the Query Editor. Using the Query Editor you will be able to specify what attribute, scalar, and vector data will be downloaded.
  7. When the Query Editor opens, review the list of objects included in your result set.
    8. If objects are missing, you will need to create a new result set to include them.
  8. Click on the icon () in the header of the Attributes, ScalarData, or VectorData columns.
  9. A window will open that will list all of the value types that can be downloaded (these lists will vary depending on the data type you select). In the example below, Country, Field, and Region attributes were selected.

    10. Click image to expand or minimize.

  10. After you've selected the data types you wish to download, click OK. The window will close and the data you selected will be added to the table in the query editor.
  11. Repeat steps 8–10 to select all the data you wish to download. When you have finished, click the Close & Load button in the Close group.

    12. Click image to expand or minimize.

  12. The Query Editor will close and your data will be loaded. Loading times will vary based on the quantity of data you choose to load.

Authentication Errors

The first time you connect to a feed, you may have to select an authentication method. After entering your feed URL and clicking OK (step 4 above), the window below will open. Follow these steps to select and use Windows Authentication to connect to your feed:

  1. When the security window opens, click on the Windows tab near the left edge of the window.

    2. Click image to expand or minimize.

  2. Toggle Use my current credentials and then click the Connect button.

    3. Click image to expand or minimize.

Make Changes to your Query

You can make changes to your query by following these steps:

  1. Select a cell in the header of the table.
  2. On the QUERY tab, in the Edit group, click Edit Query.
  3. The Query Editor will open.
  4. If the Query Settings pane is not open, Click on the View tab, and then select Query Settings in the Show group.

    5. Click image to expand or minimize.

  5. Modify your query as you would when setting it up.
  6. When you have finished making the changes you require, click the Close & Load button.

    7. Click image to expand or minimize.

  7. Your query will run again and your updated data will be added to your spreadsheet.

Additional Information

Excel User Documentation

For more information on working with Microsoft Excel, see the Microsoft website.